<?php
/**
 * Excel.php
 * Notes:
 * author: chen
 * DateTime: 2021/3/29 17:08
 * @package app\oa\controller
 */

namespace Xtsb\Cims\Excel;

use Xtsb\Cims\Exception\ApiErrorDesc;
use Xtsb\Cims\Exception\ApiException;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use PhpOffice\PhpSpreadsheet\Shared\Date;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\ReferenceHelper;

class Sheet
{
  //横向单元格标识
  const CEll_NAME = array(
    'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z',
    'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ',
    'BA', 'BB', 'BC', 'BD', 'BE', 'BF', 'BG', 'BH', 'BI', 'BJ', 'BK', 'BL', 'BM', 'BN', 'BO', 'BP', 'BQ', 'BR', 'BS', 'BT', 'BU', 'BV', 'BW', 'BX', 'BY', 'BZ',
    'CA', 'CB', 'CC', 'CD', 'CE', 'CF', 'CG', 'CH', 'CI', 'CJ', 'CK', 'CL', 'CM', 'CN', 'CO', 'CP', 'CQ', 'CR', 'CS', 'CT', 'CU', 'CV', 'CW', 'CX', 'CY', 'CZ',
    'DA', 'DB', 'DC', 'DD', 'DE', 'DF', 'DG', 'DH', 'DI', 'DJ', 'DK', 'DL', 'DM', 'DN', 'DO', 'DP', 'DQ', 'DR', 'DS', 'DT', 'DU', 'DV', 'DW', 'DX', 'DY', 'DZ'
  );

  /**
   * 使用PHPEXECL导入
   *
   * @param string $file 文件地址
   * @param int $sheet 工作表sheet(传0则获取第一个sheet)
   * @param int $columnCnt 列数(传0则自动获取最大列)
   * @param string $imageFilePath 文件地址
   * @param array $options 操作选项
   *        int sheet 工作表下标
   *        string dirname    图片保存目录
   *        true formula     是否读取公式
   * @return array
   * @throws Exception
   */
  public static function importExcel($file = '发票详情表.xlsx', $columnCnt = 0, $options = [])
  {
    try {

      $sheet = (int)($options['sheet']??0);//工作表下标
      $imageFilePath = $options['dirname'] ?? null;//图片保存目录
      $formula = (bool)($options['formula']??null);
      /* 转码 */
      $file = iconv("utf-8", "gb2312", $file);

      if (empty($file) or !file_exists($file)) {
        throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '文件不存在！');

      }

      /** @var Xlsx $objRead */
      $objRead = IOFactory::createReader('Xlsx');

      if (!$objRead->canRead($file)) {
        /** @var Xls $objRead */
        $objRead = IOFactory::createReader('Xls');

        if (!$objRead->canRead($file)) {
          throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '只支持导入Excel文件！');
        }
      }

      /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
//      empty($options) && $objRead->setReadDataOnly(true);
      /* 建立excel对象 */
      $obj = $objRead->load($file);
      /* 获取指定的sheet表 */
      $currSheet = $obj->getSheet($sheet);

//      if (isset($options['mergeCells'])) {
//        /* 读取合并行列 */
//        $options['mergeCells'] = $currSheet->getMergeCells();
//      }

      if (0 == $columnCnt) {
        /* 取得最大的列号 */
//        $columnH = $currSheet->getHighestColumn();
        /* 取得填充数据的最大的列号 */
        $columnH = $currSheet->getHighestDataColumn();
        /* 兼容原逻辑，循环时使用的是小于等于 */
        $columnCnt = Coordinate::columnIndexFromString($columnH);

        //验证文件总列数 系统文件一般不会超过120列
        if ($columnCnt > 120) {
          throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '导入Excel文件列数错误，请将数据复制到新的Excel文件再操作导入！');
        }
      }

      /* 获取总行数 */
      $rowCnt = $currSheet->getHighestRow();
      $data = [];

      /* 读取内容 */
      for ($_row = 1; $_row <= $rowCnt; $_row++) {
        $isNull = true;

        for ($_column = 1; $_column <= $columnCnt; $_column++) {
          $cellName = Coordinate::stringFromColumnIndex($_column);
          $cellId = $cellName . $_row;
          $cell = $currSheet->getCell($cellId);

//          if (isset($options['format'])) {
//            /* 获取格式 */
//            $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
//            /* 记录格式 */
//            $options['format'][$_row][$cellName] = $format;
//          }
//
//          if (isset($options['formula'])) {
//            /* 获取公式，公式均为=号开头数据 */
//            $formula = $currSheet->getCell($cellId)->getValue();
//
//            if (0 === strpos($formula, '=')) {
//              $options['formula'][$cellName . $_row] = $formula;
//            }
//          }

          if (isset($format) && 'm/d/yyyy' == $format) {
            /* 日期格式翻转处理 */
            $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
          }

//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getCalculatedValue());
//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getValue());
          if($formula){
            $data[$_row][$cellName] = $currSheet->getCell($cellId)->getValue();
            if (0 === strpos($data[$_row][$cellName], '=')) {
              $is_formula = true;
            }
          }
          if(empty($is_formula)){
            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getOldCalculatedValue());//读取缓存值
            $data[$_row][$cellName] = !empty($data[$_row][$cellName]) ? $data[$_row][$cellName] : trim($currSheet->getCell($cellId)->getCalculatedValue());

          }

          if (!empty($data[$_row][$cellName])) {
            $isNull = false;
          }
        }

        /* 判断是否整行数据为空，是的话删除该行数据 */
        if ($isNull) {
          unset($data[$_row]);
        }
      }

      //excel含有图片
      if ($imageFilePath) {
        //$imageFilePath = './files/pi/' . date('Ymd') . '/';//图片保存目录
        if (!file_exists($imageFilePath)) {
          mkdir($imageFilePath, 0777, true);
        }

        foreach ($currSheet->getDrawingCollection() as $drawing) {
          list($startColumn, $startRow) = Coordinate::coordinateFromString($drawing->getCoordinates());//获取列与行号

//          $imageFileName = $drawing->getCoordinates() . uniqid() . rand(1, 99999);//以单元格坐标作为图片名称
//          $imageFileName = $drawing->getIndexedFilename() . uniqid() . rand(1, 99999);//图片名称
          $imageFileName = $drawing->getDescription();//图片名称
          $filepath = $imageFilePath . '/' . $imageFileName . '.' . $drawing->getExtension();
          /*表格解析后图片会以资源形式保存在对象中，可以通过getImageResource函数直接获取图片资源然后写入本地文件中 */
          switch ($drawing->getExtension()) {
            case 'jpg':
            case 'jpeg':
//              $imageFileName .= '.jpeg';
              $source = imagecreatefromjpeg($drawing->getPath());
              imagejpeg($source, $filepath);
              break;
            case 'gif':
//              $imageFileName .= '.gif';
              $source = imagecreatefromgif($drawing->getPath());
              imagegif($source, $filepath);
              break;
            case 'png':
//              $imageFileName .= '.png';
              $source = imagecreatefrompng($drawing->getPath());
              imagepng($source, $filepath);
              break;
          }
          if (isset($source)) {
            imagedestroy($source);//释放内存
          }
          $data[$startRow][$startColumn] = substr($filepath, 2);//去除第一个字符后 追加到数组中去
        }
      }

      return $data;

    } catch (\Exception $e) {
      throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, $e->getMessage());

      throw $e;
    }

  }

  /**
   * Notes: 导入多个sheet表
   * author: chen
   * DateTime: 2021/10/18 13:37
   * @param string $file
   * @param array $sheets
   * @param string $imageFilePath
   * @param int $columnCnt
   * @param array $options
   * @return array
   * @throws \Exception
   */
  public static function importMultiSheetExcel($file = '发票详情表.xlsx', $sheets = [], $imageFilePath = '', $columnCnt = 0, $options = [])
  {
    try {

      /* 转码 */
      $file = iconv("utf-8", "gb2312", $file);

      if (empty($file) or !file_exists($file)) {
        throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '文件不存在！');
      }

      /** @var Xlsx $objRead */
      $objRead = IOFactory::createReader('Xlsx');

      if (!$objRead->canRead($file)) {
        /** @var Xls $objRead */
        $objRead = IOFactory::createReader('Xls');

        if (!$objRead->canRead($file)) {
          throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, '只支持导入Excel文件！');

        }
      }

      /* 如果不需要获取特殊操作，则只读内容，可以大幅度提升读取Excel效率 */
      empty($options) && $objRead->setReadDataOnly(true);
      /* 建立excel对象 */
      $obj = $objRead->load($file);

      foreach ($sheets as $sheet => $sheetName) {

        /* 获取指定的sheet表 */
//        $currSheet = $obj->getSheet($sheet);
        $currSheet = $obj->getSheetByName($sheetName);//根据sheet名称读取表数据

        if (isset($options['mergeCells'])) {
          /* 读取合并行列 */
          $options['mergeCells'] = $currSheet->getMergeCells();
        }

        if (0 == $columnCnt) {
          /* 取得最大的列号 */
          $columnH = $currSheet->getHighestColumn();
          /* 兼容原逻辑，循环时使用的是小于等于 */
          $columnCnt = Coordinate::columnIndexFromString($columnH);
        }

        /* 获取总行数 */
        $rowCnt = $currSheet->getHighestRow();
        $data = [];

        /* 读取内容 */
        for ($_row = 1; $_row <= $rowCnt; $_row++) {
          $isNull = true;

          for ($_column = 1; $_column <= $columnCnt; $_column++) {

            $cellName = Coordinate::stringFromColumnIndex($_column);
            $cellId = $cellName . $_row;
            $cell = $currSheet->getCell($cellId);

            if (isset($options['format'])) {
              /* 获取格式 */
              $format = $cell->getStyle()->getNumberFormat()->getFormatCode();
              /* 记录格式 */
              $options['format'][$_row][$cellName] = $format;
            }

            if (isset($options['formula'])) {
              /* 获取公式，公式均为=号开头数据 */
              $formula = $currSheet->getCell($cellId)->getValue();

              if (0 === strpos($formula, '=')) {
                $options['formula'][$cellName . $_row] = $formula;
              }
            }

            if (isset($format) && 'm/d/yyyy' == $format) {
              /* 日期格式翻转处理 */
              $cell->getStyle()->getNumberFormat()->setFormatCode('yyyy/mm/dd');
            }

//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getFormattedValue());
//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getCalculatedValue());
//            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getValue());
            $data[$_row][$cellName] = trim($currSheet->getCell($cellId)->getOldCalculatedValue());//读取缓存值
            $data[$_row][$cellName] = !empty($data[$_row][$cellName]) ? $data[$_row][$cellName] : trim($currSheet->getCell($cellId)->getCalculatedValue());

            if (!empty($data[$_row][$cellName])) {
              $isNull = false;
            }
          }


          /* 判断是否整行数据为空，是的话删除该行数据 */
          if ($isNull) {
            unset($data[$_row]);
          }
        }

        //excel含有图片
        if ($imageFilePath) {
          //$imageFilePath = './files/pi/' . date('Ymd') . '/';//图片保存目录
          if (!file_exists($imageFilePath)) {
            mkdir($imageFilePath, 0777, true);
          }

          foreach ($currSheet->getDrawingCollection() as $img) {

            list($startColumn, $startRow) = \PHPExcel_Cell::coordinateFromString($img->getCoordinates());//获取列与行号

            $imageFileName = $img->getCoordinates() . uniqid() . rand(1, 99999);//图片名称

            /*表格解析后图片会以资源形式保存在对象中，可以通过getImageResource函数直接获取图片资源然后写入本地文件中*/
            switch ($img->getMimeType()) {
              case 'image/jpg':
                break;
              case 'image/jpeg':
                $imageFileName .= '.jpeg';
                imagejpeg($img->getImageResource(), $imageFilePath . $imageFileName, 30);
                break;
              case 'image/gif':
                $imageFileName .= '.gif';
                imagegif($img->getImageResource(), $imageFilePath . $imageFileName);
                break;
              case 'image/png':
                $imageFileName .= '.png';
                imagepng($img->getImageResource(), $imageFilePath . $imageFileName, 9);
                break;
            }
            imagedestroy($img->getImageResource());//释放内存
            $data[$startRow][$startColumn] = substr($imageFilePath . $imageFileName, 1);//去除第一个字符后 追加到数组中去
          }
        }

        $datas[$sheetName] = $data;

        $columnCnt = 0;//计算每个sheet列号
      }

      return $datas;

    } catch (\Exception $e) {
      throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, $e->getMessage());
      throw $e;
    }

  }

  /**
   * Excel导出，TODO 可继续优化
   *
   * @param array $datas 导出数据，格式['A1' => 'XXXX公司报表', 'B1' => '序号']
   * @param string $fileName 导出文件名称
   * @param string $freezePane 冻结单元格定位
   * @param array $options 操作选项，例如：
   *        bool   print       设置打印格式
   * @param string savePath    保存路径，设置后则文件保存到服务器，不通过浏览器下载
   */
  public static function exportExcel($datas = [], $fileName = '', $freezePane = 'A2', $savePath = null, array $options = [])
  {
    try {
      if (empty($datas)) {
        return false;
      }

      set_time_limit(0);

      //横向单元格标识
      $cellName = self::CEll_NAME;

      /** @var Spreadsheet $objSpreadsheet */
      $objSpreadsheet = app(Spreadsheet::class);
      $referenceHelper = ReferenceHelper::getInstance();
      /* 设置默认文字居左，上下居中 */
      $styleArray = [
        'alignment' => [
          'horizontal' => Alignment::HORIZONTAL_CENTER,//左对齐
          'vertical' => Alignment::VERTICAL_CENTER,//水平居中
        ],
      ];
      if ($objSpreadsheet->getSheetCount() == 0) {
        $objSpreadsheet->createSheet();
      }
      $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);//设置默认文字居左，上下居中

      $objSpreadsheet->getDefaultStyle()->getAlignment()->setWrapText(true);//设置自动换行
      /* 设置Excel Sheet */
      $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

      /* 打印设置 */
      if (isset($options['print']) && $options['print']) {
        /* 设置打印为A4效果 */
        $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
        /* 设置打印时边距 */
        $pValue = 1 / 2.54;
        $activeSheet->getPageMargins()->setTop($pValue / 2);
        $activeSheet->getPageMargins()->setBottom($pValue * 2);
        $activeSheet->getPageMargins()->setLeft($pValue / 2);
        $activeSheet->getPageMargins()->setRight($pValue / 2);
      }

      /* 行数据处理 */
      $rowNo = 1;
      $mergeCells = [];
      $formula_column_arr = [];
      foreach ($datas as $rowKey => $rowItem) {
        $colNums = 0;//列合并计数器
        foreach ($rowItem as $colNo => $colItem) {
          $style = new \PhpOffice\PhpSpreadsheet\Style\Style();

          $colNum = isset($colItem['style']['colNum']) ? $colItem['style']['colNum'] - 1 : 0;//列合并数（占用列数）
          $rowNum = isset($colItem['style']['rowNum']) ? $colItem['style']['rowNum'] - 1 : 0;//数据占用行数 行合并数
          $format = isset($colItem['style']['format']) ? $colItem['style']['format'] : null;//数据格式
          $colWidth = isset($colItem['style']['colWidth']) ? $colItem['style']['colWidth'] : null;//单元格宽度
          $rowHeight = isset($colItem['style']['rowHeight']) ? $colItem['style']['rowHeight'] : null;//单元格高度
          $fontBold = isset($colItem['style']['fontBold']) ? $colItem['style']['fontBold'] : null;//字体加粗
          $fontSize = isset($colItem['style']['fontSize']) ? $colItem['style']['fontSize'] : 10;//字号
          $fontName = isset($colItem['style']['fontName']) ? $colItem['style']['fontName'] : '宋体';//字体
          $bgColor = isset($colItem['style']['bgColor']) ? $colItem['style']['bgColor'] : null;//背景颜色
          $fontColor = isset($colItem['style']['fontColor']) ? $colItem['style']['fontColor'] : null;//字体颜色
          $align = isset($colItem['style']['align']) ? $colItem['style']['align'] : null;//对齐方式
          $wrap = isset($colItem['style']['wrap']) ? $colItem['style']['wrap'] : null;//是否自动换行
          $pDataType = DataType::TYPE_STRING;//默认文本格式

          $colNos = $colNums + $colNo;//列号
          switch ($format) {
            case 'money':
              //金钱 ￥
              $colItem['value'] = (float)$colItem['value'];
              if(!is_numeric($colItem['value'])){
                $colItem['value'] = 0;
              }
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              $style->getNumberFormat()->setFormatCode('[$￥ ]#,##0.00_-');//NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1
              //水平居右
              $style->applyFromArray($styleArray);
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//金额右对齐

              break;
            case 'currency':
              //货币 单位：万
              $colItem['value'] = (float)$colItem['value'];
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              if(!is_numeric($colItem['value'])){
                $colItem['value'] = 0;
              }
              $style->getNumberFormat()->setFormatCode('0!.0," 万"');//NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1
              //水平居右
              $style->applyFromArray($styleArray);
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//金额右对齐

              break;
            case 'int':
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              if(!is_numeric($colItem['value'])){
                $colItem['value'] = 0;
              }
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//字符串左对齐
              break;
            case 'numeric':
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              $colItem['value'] = (float)$colItem['value'];
              if(!is_numeric($colItem['value'])){
                $colItem['value'] = 0;
              }
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//字符串左对齐
              break;
            case 'date':
              if ($colItem['value']) {
                $pDataType = DataType::TYPE_ISO_DATE;//日期格式
              }
              $style->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);//字符串左对齐
              break;
            default:
              $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);//字符串左对齐
              break;
          }
          $style->getFont()->setSize($fontSize);
          $style->getFont()->setName($fontName);
          ##数据可以不处理，直接跳过，但是必须先进行单元格格式的设置，确保所有单元格数据格式一致
          if (!isset($colItem['value']) || is_array($colItem['value'])) {

            $activeSheet->setConditionalStyles($cellName[$colNos] . $rowNo, $style);
            continue;
          }

          $formula = (bool)($colItem['style']['formula']??null);//是否设置公式
          $formula_column = $colItem['style']['formula_column']??null;//是否设置公式
          if($formula){
            $activeSheet->setCellValueExplicit($cellName[$colNos] . $rowNo, $colItem['value'], DataType::TYPE_FORMULA);//填写公式
          }else if($formula_column){
            $param = $formula_column_arr[$colItem['value']]??null;
            if($param){
              $formula_column = $referenceHelper->updateFormulaReferences($param['formula'], $param['beforeCellAddress'], $param['numberOfColumns'], $param['numberOfRows']);
              $formula_column_arr[$colItem['value']]['numberOfRows']++;
            }else{
              $formula_column = $colItem['value'];
              $formula_column_arr[$colItem['value']] = [
                'formula'=>$colItem['value'],
                'beforeCellAddress'=>$cellName[$colNos] . $rowNo,
                'numberOfColumns'=>0,
                'numberOfRows'=>1,
              ];
            }
            $activeSheet->setCellValueExplicit($cellName[$colNos] . $rowNo, $formula_column,DataType::TYPE_FORMULA);//填写公式
          }else{
            $activeSheet->setCellValueExplicit($cellName[$colNos] . $rowNo, $colItem['value'], $pDataType);//填值
          }

          if((bool)($colItem['style']['hidden']??null)){//是否隐藏列
            $activeSheet->getColumnDimension($cellName[$colNos])->setVisible(false);
          }
//          $activeSheet->setCellValue($fKey, $fItem);

          //设置对齐方式
          if (isset($align)) {
//            halt($align);
            $style->getAlignment()->setHorizontal($align);//单元格对齐方式
          }

          if (isset($wrap)) {
            $style->getAlignment()->setWrapText($wrap);//单元格是否换行
          }

          //设置宽度
          if (isset($colWidth)) {
            $activeSheet->getColumnDimension($cellName[$colNos])->setWidth($colWidth);
            unset($colWidth);
          }

          //设置行高
          if (isset($rowHeight)) {
            $activeSheet->getRowDimension($rowNo)->setRowHeight($rowHeight);
            unset($rowHeight);
          }

          //设置背景色
          if (isset($bgColor)) {
            switch ($bgColor) {
              case 'red':
                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_RED);
                break;
              case 'green':
                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);
                break;
              case 'yellow':
                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_YELLOW);
                break;
              case 'blue':
                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('4288ce');
//                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_BLUE);
                break;
              default:
                $style->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($bgColor);
                break;
            }
            unset($bgColor);
          }

          //设置文字颜色
          if (isset($fontColor)) {
            switch ($fontColor) {
              case 'red':
                $style->getFont()->getColor()->setARGB(Color::COLOR_RED);
                break;
              case 'green':
                $style->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
                break;
              case 'yellow':
                $style->getFont()->getColor()->setARGB(Color::COLOR_YELLOW);
                break;
              case 'blue':
                $style->getFont()->getColor()->setARGB(Color::COLOR_BLUE);
                break;
              default:
                $style->getFont()->getColor()->setARGB($fontColor);
                break;
            }
            unset($fontColor);
          }

          //字体加粗
          if (isset($fontBold)) {
            $style->getFont()->setBold(true);
            unset($fontBold);
          }
          $activeSheet->duplicateStyle($style,$cellName[$colNos] . $rowNo);

          //设置需合并单元格数组
          if ($colNum > 0 || $rowNum > 0) {
            $mergeCells[] = $cellName[$colNos] . $rowNo . ':' . $cellName[$colNos + $colNum] . ($rowNo + $rowNum);
          }
          $highestColRow = $cellName[$colNos + $colNum] . ($rowNo + $rowNum);//最大宽度（外框线定位）
          $colNums += $colNum;//根据列合并数 更新列号
        }
        unset($datas[$rowKey]);
        $rowNo++;
      }
      unset($style);

//      halt($mergeCells);
//      exit();
      unset($datas);

      /* 设置锁定行 */
      if (isset($freezePane)) {
        $activeSheet->freezePane($freezePane);
        unset($freezePane);
      }

      /* 合并行列处理 */
      if (!empty($mergeCells)) {
        $activeSheet->setMergeCells($mergeCells);
      }
      unset($mergeCells);

      /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
      $border = [
        'borders' => [
          'allBorders' => [
            'borderStyle' => Border::BORDER_THIN, // 设置border样式
            'color' => ['argb' => 'FF000000'], // 设置border颜色
          ],
        ],
      ];
      $highestBorder = $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();

      $setBorder = 'A1:' . $highestBorder;
//      $setBorder = 'A1:' . (isset($highestColRow) ? $highestColRow : $highestBorder);

      $activeSheet->getStyle($setBorder)->applyFromArray($border);

//文件名称
      $fileName = !empty($fileName) ? $fileName . '.xlsx' : (date('YmdHis') . '.xlsx');

      if (!isset($options['savePath'])) {
        /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header(
          "Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName)
        );
        header('Cache-Control: max-age=0');//禁止缓存
        $savePath = 'php://output';
      } else {
        $savePath = $options['savePath'];
      }

      ob_clean();
      ob_start();
      $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
      $objWriter->save($savePath);
      /* 释放内存 */
      $objSpreadsheet->disconnectWorksheets();
      unset($objSpreadsheet);
      $xlsxdata = ob_get_contents();//返回输出缓冲区的内容
//      ob_end_flush();// 冲刷出（送出）输出缓冲区内容并关闭缓冲
      ob_end_clean();//清空（擦除）缓冲区并关闭输出缓冲
      //返回文件字符串
//      return $fileName . "|data:application/vnd.ms-excel;base64," . base64_encode($xlsxdata);
      return base64_encode($xlsxdata);
    } catch (\Exception $e) {
//      dd($e->getMessage());
      throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, $e->getMessage());

      return false;
    }
  }

  //Excel导出多个工作表
  public static function exportExcelMore($datas = [], $fileName = '', $freezePane = 'A2', $savePath = null, array $options = [])
  {
//    try {
    if (empty($datas)) {
      return false;
    }

    set_time_limit(0);

    //横向单元格标识
    $cellName = self::CEll_NAME;

    /** @var Spreadsheet $objSpreadsheet */
    $objSpreadsheet = app(Spreadsheet::class);

    foreach ($datas as $key=>$item){
      /* 设置默认文字居左，上下居中 */
      $styleArray = [
        'alignment' => [
          'horizontal' => Alignment::HORIZONTAL_CENTER,//左对齐
          'vertical' => Alignment::VERTICAL_CENTER,//水平居中
        ],
      ];
      $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);//设置默认文字居左，上下居中

      $objSpreadsheet->getDefaultStyle()->getAlignment()->setWrapText(true);//设置自动换行

      $title = $item['title'] ?? 'sheet';
      unset($item['title']);

      /* 设置Excel Sheet */
      if($key > 0){
        $objSpreadsheet->createSheet();

        $myWorkSheet = new Worksheet($objSpreadsheet, $title);

        $objSpreadsheet->addSheet($myWorkSheet, $key);
      }else{
        $objSpreadsheet->getActiveSheet()->setTitle($title);
      }

      $activeSheet = $objSpreadsheet->setActiveSheetIndex($key);

      /* 打印设置 */
      if (isset($options['print']) && $options['print']) {
        /* 设置打印为A4效果 */
        $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
        /* 设置打印时边距 */
        $pValue = 1 / 2.54;
        $activeSheet->getPageMargins()->setTop($pValue / 2);
        $activeSheet->getPageMargins()->setBottom($pValue * 2);
        $activeSheet->getPageMargins()->setLeft($pValue / 2);
        $activeSheet->getPageMargins()->setRight($pValue / 2);
      }

      /* 行数据处理 */
      $rowNo = 1;
      $mergeCells = [];
      foreach ($item as $rowItem) {
        $colNums = 0;//列合并计数器
        foreach ($rowItem as $colNo => $colItem) {
          if (!isset($colItem['value']) || is_array($colItem['value'])) {
            continue;
          }

          $colNum = isset($colItem['style']['colNum']) ? $colItem['style']['colNum'] - 1 : 0;//列合并数（占用列数）
          $rowNum = isset($colItem['style']['rowNum']) ? $colItem['style']['rowNum'] - 1 : 0;//数据占用行数 行合并数
          $format = isset($colItem['style']['format']) ? $colItem['style']['format'] : null;//数据格式
          $colWidth = isset($colItem['style']['colWidth']) ? $colItem['style']['colWidth'] : null;//单元格宽度
          $rowHeight = isset($colItem['style']['rowHeight']) ? $colItem['style']['rowHeight'] : null;//单元格高度
          $fontBold = isset($colItem['style']['fontBold']) ? $colItem['style']['fontBold'] : null;//字体加粗
          $fontSize = isset($colItem['style']['fontSize']) ? $colItem['style']['fontSize'] : 11;//字号
          $fontName = isset($colItem['style']['fontName']) ? $colItem['style']['fontName'] : '宋体';//字体
          $bgColor = isset($colItem['style']['bgColor']) ? $colItem['style']['bgColor'] : null;//背景颜色
          $fontColor = isset($colItem['style']['fontColor']) ? $colItem['style']['fontColor'] : null;//字体颜色
          $align = isset($colItem['style']['align']) ? $colItem['style']['align'] : null;//对齐方式
          $wrap = isset($colItem['style']['wrap']) ? $colItem['style']['wrap'] : null;//是否自动换行
          $pDataType = DataType::TYPE_STRING;//默认文本格式

          $colNos = $colNums + $colNo;//列号
          switch ($format) {
            case 'money':
              //金钱 ￥
              $colItem['value'] = (float)$colItem['value'];
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getNumberFormat()->setFormatCode('[$￥ ]#,##0.00_-');//NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1
              //水平居右
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->applyFromArray($styleArray);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//金额右对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);

              break;
            case 'currency':
              //货币 单位：万
              $colItem['value'] = (float)$colItem['value'];
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getNumberFormat()->setFormatCode('0!.0," 万"');//NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1
              //水平居右
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->applyFromArray($styleArray);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//金额右对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);

              break;
            case 'int':
              $pDataType = DataType::TYPE_NUMERIC;//整数格式
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);//字符串左对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);

              break;
            case 'date':
              if ($colItem['value']) {
                $pDataType = DataType::TYPE_ISO_DATE;//日期格式
              }
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getNumberFormat()->setFormatCode(NumberFormat::FORMAT_DATE_YYYYMMDDSLASH);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);//字符串左对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);
              break;
            case 'string':
              //str
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);//字符串左对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);
              break;
            default:
              //str
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);//字符串左对齐
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setSize($fontSize);
              $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setName($fontName);
              break;
          }

          $activeSheet->setCellValueExplicit($cellName[$colNos] . $rowNo, $colItem['value'], $pDataType);//填值
//          $activeSheet->setCellValue($fKey, $fItem);

          //设置对齐方式
          if (isset($align)) {
//            halt($align);
            $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setHorizontal($align);//单元格对齐方式
          }

          if (isset($wrap)) {
            $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getAlignment()->setWrapText($wrap);//单元格是否换行
          }

          //设置宽度
          if (isset($colWidth)) {
            $activeSheet->getColumnDimension($cellName[$colNos])->setWidth($colWidth);
            unset($colWidth);
          }

          //设置行高
          if (isset($rowHeight)) {
            $activeSheet->getRowDimension($rowNo)->setRowHeight($rowHeight);
            unset($rowHeight);
          }

          //设置背景色
          if (isset($bgColor)) {
            switch ($bgColor) {
              case 'red':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_RED);
                break;
              case 'green':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_GREEN);
                break;
              case 'yellow':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_YELLOW);
                break;
              case 'blue':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('4288ce');
//                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB(Color::COLOR_BLUE);
                break;
              default:
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB($bgColor);
                break;
            }
            unset($bgColor);
          }

          //设置文字颜色
          if (isset($fontColor)) {
            switch ($fontColor) {
              case 'red':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->getColor()->setARGB(Color::COLOR_RED);
                break;
              case 'green':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->getColor()->setARGB(Color::COLOR_GREEN);
                break;
              case 'yellow':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->getColor()->setARGB(Color::COLOR_YELLOW);
                break;
              case 'blue':
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->getColor()->setARGB(Color::COLOR_BLUE);
                break;
              default:
                $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->getColor()->setARGB($fontColor);
                break;
            }
            unset($fontColor);
          }

          //字体加粗
          if (isset($fontBold)) {
            $activeSheet->getStyle($cellName[$colNos] . $rowNo)->getFont()->setBold(true);
            unset($fontBold);
          }

          //设置需合并单元格数组
          if ($colNum > 0 || $rowNum > 0) {
            $mergeCells[] = $cellName[$colNos] . $rowNo . ':' . $cellName[$colNos + $colNum] . ($rowNo + $rowNum);
          }
          $highestColRow = $cellName[$colNos + $colNum] . ($rowNo + $rowNum);//最大宽度（外框线定位）
          $colNums += $colNum;//根据列合并数 更新列号
          unset($colItem);
        }
        $rowNo++;
      }
      unset($item);

      /* 设置锁定行 */
      if (isset($freezePane)) {
        $activeSheet->freezePane($freezePane);
        unset($freezePane);
      }

      /* 合并行列处理 */
      if (!empty($mergeCells)) {
        $activeSheet->setMergeCells($mergeCells);
      }

      /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
      $border = [
        'borders' => [
          'allBorders' => [
            'borderStyle' => Border::BORDER_THIN, // 设置border样式
            'color' => ['argb' => 'FF000000'], // 设置border颜色
          ],
        ],
      ];
      $highestBorder = $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();

      $setBorder = 'A1:' . $highestBorder;
//      $setBorder = 'A1:' . (isset($highestColRow) ? $highestColRow : $highestBorder);

      $activeSheet->getStyle($setBorder)->applyFromArray($border);
    }

//      halt($mergeCells);
//      exit();
    unset($datas);

//文件名称
    $fileName = !empty($fileName) ? $fileName . '.xlsx' : (date('YmdHis') . '.xlsx');

    if (!isset($options['savePath'])) {
      /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
      header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      header(
        "Content-Disposition:attachment;filename=" . iconv("utf-8", "GB2312//TRANSLIT", $fileName)
      );
      header('Cache-Control: max-age=0');//禁止缓存
      $savePath = 'php://output';
    } else {
      $savePath = $options['savePath'];
    }

    ob_clean();
    ob_start();
    $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
    $objWriter->save($savePath);
    /* 释放内存 */
    $objSpreadsheet->disconnectWorksheets();
    unset($objSpreadsheet);
    $xlsxdata = ob_get_contents();//返回输出缓冲区的内容
//      ob_end_flush();// 冲刷出（送出）输出缓冲区内容并关闭缓冲
    ob_end_clean();//清空（擦除）缓冲区并关闭输出缓冲
    //返回文件字符串
//      return $fileName . "|data:application/vnd.ms-excel;base64," . base64_encode($xlsxdata);
    return base64_encode($xlsxdata);
//    } catch (\Exception $e) {
////      dd($e->getMessage());
//      throw new ApiException(ApiErrorDesc::ERROR_DEFAULT, $e->getMessage());
//
//      return false;
//    }
  }
}
